<?php
namespace App\Http\Controllers;

use Laravel\Lumen\Routing\Controller as BaseController;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;
use App\Http\Model\AudioApi;
use App\Http\Model\Base;
use Exception;
class FishController extends Base
{
    //
    public function __construct(array $attributes = [])
    {
        parent::__construct($this->table, $attributes);
    }
    public function fishwinner(){
      $sql = "SELECT
          f.user_id,
          0 + CAST(fp.multiple AS CHAR) AS multiple,
          prize_id,
          u.nickname,
          u.portrait,
          u.uuid
        FROM
          zb_fishing_prize fp
        INNER JOIN (
          SELECT
            user_id,
            max(prize_id) AS prize_id
          FROM
            `zb_fishing_log`
          WHERE
          '2020-05-20 00:00:00'<= create_at AND create_at<= '2020-05-26 00:00:00'
          GROUP BY
            user_id
        ) f ON fp.id = f.prize_id
        INNER JOIN zb_user as u ON u.user_id = f.user_id ORDER BY
        fp.multiple DESC limit 10";
        // var_dump($sql);
        // dd($sql);
      $data = DB::connection('mysql_kkaudio')->select($sql);
      $data = self::objToArray($data);

      foreach($data as &$item) {

        $sql_num = "
        SELECT
          min(create_at) as create_at,count(id) as num
        FROM
          `zb_fishing_log`
        WHERE
        user_id ={$item['user_id']}
        AND prize_id = {$item['prize_id']}
        ";
        $NUM = DB::connection('mysql_kkaudio')->select($sql_num);
        $NUM = self::objToArray($NUM);
        $item['num'] = $NUM[0]['num'];
        $item['create'] = $NUM[0]['create_at'];

        if(mb_strlen($item['nickname'],"utf-8")>4){
          $item['nickname'] = mb_substr($item['nickname'],0,4,'utf-8').'..';
        }else{
          $item['nickname'] = mb_substr($item['nickname'],0,4,'utf-8');
        }
        $portrait = json_decode($item['portrait'], true);
        $item['portrait'] = build_image($portrait[0]);
      }

      // dd(array_column($item,'create_at'));
      $ww = array_multisort(array_column($data,'num'),SORT_DESC,$data);
      $ww = $data;
      //dd($ww);
      unset($item);
      return $data;

    }
    public function fishPk(){
      $sql = "SELECT
        SUM(cat.reward - cat.diamond) AS score,
        cat.user_id,
        user.uuid,
        user.nickname,
        `user`.portrait
      FROM
        zb_cat_buy AS cat
      JOIN
      zb_user as user ON `user`.user_id = cat.user_id
      WHERE
      '2020-05-20 00:00:00'<= create_at AND create_at<= '2020-05-26 00:00:00'
      GROUP BY
        user_id
      ORDER BY
        score DESC limit 10";
      $data = DB::connection('mysql_kkaudio')->select($sql);
      $data = self::objToArray($data);
      foreach($data as &$item) {
        if(mb_strlen($item['nickname'],"utf-8")>4){
          $item['nickname'] = mb_substr($item['nickname'],0,4,'utf-8').'..';
        }else{
          $item['nickname'] = mb_substr($item['nickname'],0,4,'utf-8');
        }
        $portrait = json_decode($item['portrait'], true);
        $item['portrait'] = build_image($portrait[0]);
      }
      unset($item);
      return $data;
    }
    public function fishGift(){
      $start = strtotime('2020-05-19 00:00:00');
      $sql = 'SELECT
        COUNT(stream_id) AS num,
        SUM(gift_num) as score,
        gs.user_id,gs.from_user_id,
        u.nickname as nicknamefrom,
        u.portrait as portraitfrom,
      `user`.nickname as nicknameto,
      `user`.portrait as portraitto
      FROM
        zb_gift_stream AS gs
      INNER JOIN 
        zb_user u on u.user_id = gs.from_user_id
      INNER JOIN 
        zb_user user on user.user_id = gs.user_id
      WHERE
        gs.gift_id = 187 and gs.change_timestamp = '.$start.'
      GROUP BY
        gs.user_id, gs.from_user_id
      ORDER BY
      score DESC';
      $data = DB::connection('mysql_kkaudio')->select($sql);
      $data = self::objToArray($data);
      // var_dump($sql);die;
      foreach($data as &$item) {
        
        if(mb_strlen($item['nicknamefrom'],"utf-8")>4){
          $item['nicknamefrom'] = mb_substr($item['nicknamefrom'],0,4,'utf-8').'..';
        }else{
          $item['nicknamefrom'] = mb_substr($item['nicknamefrom'],0,4,'utf-8');
        }
        
        if(mb_strlen($item['nicknameto'],"utf-8")>4){
          $item['nicknameto'] = mb_substr($item['nicknameto'],0,4,'utf-8').'..';
        }else{
          $item['nicknameto'] = mb_substr($item['nicknameto'],0,4,'utf-8');
        }
        
        $portraitfrom = json_decode($item['portraitfrom'], true);
        $item['portraitfrom'] = build_image($portraitfrom[0]);
        $portraitto = json_decode($item['portraitto'], true);
        $item['portraitto'] = build_image($portraitto[0]);
      }
      unset($item);
      // var_dump($data);
      return $data;

    }
}